namespace CISLib
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageProduct
    {
        public static SqlDataReader GetProduct()
        {
            string strSQL = "Select * from TB_Product";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static Product[] GetProductAryViaId(Guid customerId)
        {
            SqlDataReader MyReader = null;
            ArrayList productList = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select TB_CustomerProduct.ProductId from TB_CustomerProduct where TB_CustomerProduct.CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                Product product = new Product();
                product = GetProductViaId(MyReader.GetGuid(0));
                productList.Add(product);
            }
            MyDB.DBClose();
            return (Product[]) productList.ToArray(typeof(Product));
        }

        public static Product GetProductViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            Product product = new Product();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_Product where ProductId = @ProductId";
            MyDB.AddParameters(id, "ProductId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                product.ProductId = MyReader.GetGuid(0);
                product.ProductNameCn = MyReader.GetString(1);
                product.ProductNameEn = MyReader.GetString(2);
            }
            MyDB.DBClose();
            return product;
        }

        public static void InsertNewProduct(string name, string scName)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "ProductNameCn", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "ProductNameEn", SqlDbType.VarChar);
            MyDB.AddParameters(id, "ProductId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_Product (ProductNameCn,ProductNameEn,ProductId) values (@ProductNameCn,@ProductNameEn,@ProductId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistSameProductName(string name)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "ProductNameCn", SqlDbType.VarChar);
            string strSQL = "select * from TB_Product where ProductNameCn = @ProductNameCn";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExitProductInCustomerProductViaId(Guid customerId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerProduct where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveProductViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "ProductId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_Product where ProductId = @ProductId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateProduct(string name, string scName, Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "ProductNameCn", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "ProductNameEn", SqlDbType.VarChar);
            MyDB.AddParameters(id, "ProductId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_Product set ProductNameCn = @ProductNameCn,ProductNameEn = @ProductNameEn where ProductId = @ProductId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

